package com.sl.au.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Chance;
import com.sl.au.entity.ChanceDelivery;
import com.sl.au.entity.ChanceDeliveryTemp;
import com.sl.au.entity.ChanceManagerDelivery;

@Transactional(readOnly = true)
public interface ChanceDeliveryRepository extends JpaRepository<ChanceDelivery, String> {

	/**
	 * 获取处长所在部门的Chance
	 * 
	 * @param userSuperior
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%'")
	Page<Chance> findUserChance(String userSuperior, String key, String isReceived, Pageable pageable);
	
	@Query(" select c from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%' and c.chanceType = ?4")
	Page<Chance> findUserChanceOne(String userSuperior, String key, String isReceived,int targetType, Pageable pageable);

	/**
	 * 获取处长所在部门的Chance条数
	 * 
	 * @param userSuperior
	 * @return
	 */
	@Query(" select count(*) from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%'")
	Long getAllChanceCount(String userSuperior, String key,String isReceived);
	
	@Query(" select count(*) from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%' and c.chanceType = ?4")
	Long getAllChanceCountOne(String userSuperior, String key,String isReceived,int targetType);

	/**获取科长的chance
	 * @param string
	 * @param string2
	 * @param string3
	 * @param string4
	 * @param string5
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%' and ( c.saleChance.user.saleArea.id in ( select s.saleArea.id from SectionArea s where s.section.id=?4 ) )")
	Page<Chance> findUserChance(String userSuperior, String key, String isReceived, String sectionId,Pageable pageable);
	@Query(" select c from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%' and ( c.saleChance.user.saleArea.id in ( select s.saleArea.id from SectionArea s where s.section.id='?4' ) ) and c.chanceType = ?5")
	Page<Chance> findUserChance(String userSuperior, String key, String isReceived, String sectionId,int targetType,Pageable pageable);
	
	/**
	 * 获取科长所在部门的Chance条数
	 * 
	 * @param userSuperior
	 * @return
	 */
	@Query(" select count(*) from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%' and ( c.saleChance.user.saleArea.id in ( select s.saleArea.id from SectionArea s where s.section.id=?4 ) )")
	Long getAllChanceCount(String userSuperior, String key,String isReceived,String sectionId);
	
	@Query(" select count(*) from ChanceDeliveryTemp c where (receiveSuperior =?1 or (c.saleChance.user.saleArea.superior =?1 and c.chanceType = 2)) and (c.saleChance.name like '%'+?2+'%' "
			+ "or c.saleChance.source like '%'+?2+'%' or c.saleChance.sourceUnit like '%'+?2+'%' "
			+ "or c.saleChance.orderUnit like '%'+?2+'%' or c.saleChance.orderPerson like '%'+?2+'%'"
			+ "or c.saleChance.creator like '%'+?2+'%') and c.isReceived like '%'+?3+'%' and ( c.saleChance.user.saleArea.id in ( select s.saleArea.id from SectionArea s where s.section.id=?4 ) ) and c.chanceType = ?5")
	Long getAllChanceCount(String userSuperior, String key,String isReceived,String sectionId,int targetType);
	
	/**
	 * 获取销售经理的Chance
	 * 
	 * @param userSuperior
	 * @param pageable
	 * @return
	 */
	@Query(" select c from ChanceDelivery c where c.saleArea.superior =?1 "
			+ "and c.saleArea.areaName =?2 and c.rejectReason is null and (c.saleChance.name like '%'+?3+'%' "
			+ "or c.saleChance.source like '%'+?3+'%' or c.saleChance.sourceUnit like '%'+?3+'%' "
			+ "or c.saleChance.orderUnit like '%'+?3+'%' or c.saleChance.orderPerson like '%'+?3+'%'"
			+ "or c.saleChance.creator like '%'+?3+'%') and c.isReceived like '%'+?4+'%'")
	Page<ChanceDelivery> findManagerChance(String userSuperior, String userArea, String key,String isReceived, Pageable pageable);
	
	@Query(" select c from ChanceDelivery c where (c.saleArea.superior =?1 "
			+ "and c.saleArea.areaName =?2 and c.rejectReason is null and (c.saleChance.name like '%'+?3+'%' "
			+ "or c.saleChance.source like '%'+?3+'%' or c.saleChance.sourceUnit like '%'+?3+'%' "
			+ "or c.saleChance.orderUnit like '%'+?3+'%' or c.saleChance.orderPerson like '%'+?3+'%'"
			+ "or c.saleChance.creator like '%'+?3+'%') and c.isReceived like '%'+?4+'%') and c.targetType = ?5")
	Page<ChanceDelivery> findManagerChance(String userSuperior, String userArea, String key,String isReceived,int targetType, Pageable pageable);

	/**
	 * 获取销售经理的Chance条数
	 * 
	 * @param userSuperior
	 * @return
	 */
	@Query(" select count(*) as num from ChanceDelivery c where c.saleArea.superior =?1 "
			+ "and c.saleArea.areaName =?2 and c.rejectReason is null and (c.saleChance.name like '%'+?3+'%' "
			+ "or c.saleChance.source like '%'+?3+'%' or c.saleChance.sourceUnit like '%'+?3+'%' "
			+ "or c.saleChance.orderUnit like '%'+?3+'%' or c.saleChance.orderPerson like '%'+?3+'%'"
			+ "or c.saleChance.creator like '%'+?3+'%') and c.isReceived like '%'+?4+'%'")
	Long getAllManagerChanceCount(String userSuperior, String userArea, String key,String isReceived);
	
	@Query(" select count(*) as num from ChanceDelivery c where (c.saleArea.superior =?1 "
			+ "and c.saleArea.areaName =?2 and c.rejectReason is null and (c.saleChance.name like '%'+?3+'%' "
			+ "or c.saleChance.source like '%'+?3+'%' or c.saleChance.sourceUnit like '%'+?3+'%' "
			+ "or c.saleChance.orderUnit like '%'+?3+'%' or c.saleChance.orderPerson like '%'+?3+'%'"
			+ "or c.saleChance.creator like '%'+?3+'%') and c.isReceived like '%'+?4+'%') and c.targetType = ?5")
	Long getAllManagerChanceCount(String userSuperior, String userArea, String key,String isReceived,int targetType);

	/**
	 * 获取信息片区分配弹窗的SaleArea数据
	 * 
	 * @param chanceId
	 * @param pageable
	 * @return
	 */
	@Query("select c from ChanceDelivery c where c.saleChance.id =?1 and c.targetType =?2 "
			+ "and c.saleArea.superior = ?3")
	Page<ChanceDelivery> findModuleArea(String chanceId, int targetType, String userSuperior, Pageable pageable);
	@Query("select count(*) from ChanceDelivery c where c.saleChance.id =?1 and c.targetType =?2 "
			+ "and c.saleArea.superior = ?3")
	Long getModuleAreaCount(String chanceId, int targetType, String userSuperior);

	/**
	 * 获取信息发起协作弹窗的SaleArea数据
	 * 
	 * @param chanceId
	 * @param pageable
	 * @return
	 */
	@Query("select c from ChanceDeliveryTemp c where c.saleChance.id =?1 and c.chanceType =1")
	Page<ChanceDeliveryTemp> findModuleAreaCoop(String chanceId, Pageable pageable);
	@Query("select count(*) from ChanceDeliveryTemp c where c.saleChance.id =?1 and c.chanceType =1")
	Long getModuleAreaCoopCount(String chanceId);

	/**
	 * 验证选中片区是否被分配过
	 * 
	 * @param araeId,chanceId
	 * @return
	 */
	@Query("select c from ChanceDelivery c where c.saleArea.id =?1 and c.saleChance.id =?2")
	ChanceDelivery validArea(String areaId, String chanceId);
	/**
	 * 得到机会已经分配的片区
	 * 
	 * @param chanceId
	 * @return
	 */
	@Query("select c.saleArea.id from ChanceDelivery c where  c.saleChance.id =?1")
	List<String> getDistributedSaleAreas(String chanceId);
	/**
	 * 验证是否可以传递部门
	 * 
	 * @param chanceId
	 * @return
	 */
	@Query("select count(*) from ChanceDelivery c where c.saleChance.id =?1")
	int validSuperior(String chanceId);


	/**机会已经发起协作的部门
	 * @param chanceId
	 * @return
	 */
	@Query("select c.receiveSuperior from ChanceDeliveryTemp c where c.saleChance.id =?1 and c.chanceType =1")
	List<String> findCooperatedSuperiors(String chanceId);

	/**
	 * 验证选中部门是否已发起协作
	 * 
	 * @param araeId,chanceId
	 * @return
	 */
	@Query("select c from ChanceDeliveryTemp c where c.receiveSuperior =?2 and c.saleChance.id =?1 and c.chanceType =1")
	ChanceDeliveryTemp validCoopSuperior(String chanceId, String superior);
	
	
	/**
	 * 验证选中员工是否已分配
	 * 
	 * @param userId,chanceId
	 * @return
	 */
	@Query("select c from ChanceManagerDelivery c where c.user.id =?2 and c.saleChance.id =?1")
	ChanceManagerDelivery validUser(String chanceId, String userId);
	
	/*
	 * 获得新增销售信息数量（一个时间段）
	 */
	//本人的
	@Query("select count(*) from ChanceDeliveryTemp c where c.saleChance.user.id =?1 and (c.createTime >= ?2 and c.createTime <= ?3)")
	long getMyNewChanceCount(String userId,Date start,Date end);
	
	//本部门的
	@Query("select count(*) from ChanceDeliveryTemp c where c.saleChance.user.saleArea.superior =?1 and (c.createTime >= ?2 and c.createTime <= ?3)")
	long getNewChanceCount(String superior,Date start,Date end);
	
	//本科室的
	@Query("select count(*) from ChanceDeliveryTemp c where ( c.saleChance.user.saleArea.id in ( select s.saleArea.id from SectionArea s where s.section.id=?1 ) ) and (c.createTime >= ?2 and c.createTime <= ?3)")
	long getSectionNewChanceCount(String sectionId,Date start,Date end);
	
	//本片区的
	@Query("select count(*) from ChanceDeliveryTemp c where ( c.saleChance.user.saleArea.id =?1 ) and (c.createTime >= ?2 and c.createTime <= ?3)")
	long getAreaNewChanceCount(String areaId,Date start,Date end);
	
	//所有的
	@Query("select count(*) from ChanceDeliveryTemp c where c.createTime >= ?1 and c.createTime <= ?2")
	long getAllNewChanceCount(Date start,Date end);

	@Query("select c from ChanceDeliveryTemp c where c.saleChance.id = ?1")
	ChanceDeliveryTemp findBysaleChance(String saleChance);
	
	
	
}
